const Service = require('egg').Service;
const { Op } = require('sequelize');
class AlumniService extends Service {
    // 创建校友
    async create(alumniData) {
        const { ctx } = this;

        // 检查学号是否已存在
        const existingAlumni = await ctx.model.Alumni.findOne({
            where: { student_id: alumniData.student_id }
        });
        if (existingAlumni) {
            throw new Error('学号已存在');
        }

        // 如果提供了手机号，检查手机号是否已存在
        if (alumniData.phone) {
            const existingPhone = await ctx.model.Alumni.findOne({
                where: { phone: alumniData.phone }
            });
            if (existingPhone) {
                throw new Error('手机号已存在');
            }
        }

        // 创建校友 - 默认状态为1（未认证）
        const alumni = await ctx.model.Alumni.create({
            ...alumniData,
            status: alumniData.status !== undefined ? alumniData.status : 1, // 默认未认证
            created_at: new Date(),
            updated_at: new Date(),
        });

        return alumni;
    }

    // 获取校友列表
    async getAlumnis(query) {
        const { ctx } = this;
        const {
            page = 1,
            pageSize = 10,
            student_id,
            real_name,
            grade,
            major,
            college,
            phone,
            status,
            keyword // 通用搜索关键词
        } = query;

        const where = {};

        // 构建查询条件
        if (keyword) {
            // 通用搜索：学号、姓名、手机号
            where[Op.or] = [
                { student_id: { [Op.like]: `%${keyword}%` } },
                { real_name: { [Op.like]: `%${keyword}%` } },
                { phone: { [Op.like]: `%${keyword}%` } }
            ];
        } else {
            // 精确搜索
            if (student_id) where.student_id = { [Op.like]: `%${student_id}%` };
            if (real_name) where.real_name = { [Op.like]: `%${real_name}%` };
            if (phone) where.phone = { [Op.like]: `%${phone}%` };
        }

        // 其他筛选条件
        if (grade) where.grade = grade;
        if (major) where.major = { [Op.like]: `%${major}%` };
        if (college) where.college = { [Op.like]: `%${college}%` };
        if (status !== undefined) where.status = parseInt(status);

        const options = {
            where,
            limit: parseInt(pageSize),
            offset: (parseInt(page) - 1) * parseInt(pageSize),
            order: [['created_at', 'ASC']]
        };

        const { count, rows } = await ctx.model.Alumni.findAndCountAll(options);

        return {
            list: rows,
            pagination: {
                page: parseInt(page),
                pageSize: parseInt(pageSize),
                total: count,
                totalPages: Math.ceil(count / pageSize)
            }
        };
    }

    // 根据ID获取校友详情
    async getAlumniById(id) {
        const { ctx } = this;

        const alumni = await ctx.model.Alumni.findByPk(id);
        if (!alumni) {
            throw new Error('校友不存在');
        }

        return alumni;
    }

    // 根据学号获取校友详情
    async getAlumniByStudentId(studentId) {
        const { ctx } = this;

        const alumni = await ctx.model.Alumni.findOne({
            where: { student_id: studentId }
        });
        if (!alumni) {
            throw new Error('校友不存在');
        }

        return alumni;
    }

    // 更新校友信息
    async updateAlumni(id, updateData) {
        const { ctx } = this;

        // 检查校友是否存在
        const alumni = await ctx.model.Alumni.findByPk(id);
        if (!alumni) {
            throw new Error('校友不存在');
        }

        // 如果更新学号，检查是否重复
        if (updateData.student_id && updateData.student_id !== alumni.student_id) {
            const existingAlumni = await ctx.model.Alumni.findOne({
                where: { student_id: updateData.student_id }
            });
            if (existingAlumni) {
                throw new Error('学号已存在');
            }
        }

        // 如果更新手机号，检查是否重复
        if (updateData.phone && updateData.phone !== alumni.phone) {
            const existingPhone = await ctx.model.Alumni.findOne({
                where: { phone: updateData.phone }
            });
            if (existingPhone) {
                throw new Error('手机号已存在');
            }
        }

        // 更新校友信息
        await alumni.update({
            ...updateData,
            updated_at: new Date()
        });

        return alumni;
    }

    // 删除校友
    async deleteAlumni(id) {
        const { ctx } = this;

        const alumni = await ctx.model.Alumni.findByPk(id);
        if (!alumni) {
            throw new Error('校友不存在');
        }

        await alumni.destroy();
        return true;
    }

    // 批量导入校友
    async batchCreate(alumniList) {
        const { ctx } = this;

        // 验证学号是否重复
        const studentIds = alumniList.map(item => item.student_id);
        const existingAlumnis = await ctx.model.Alumni.findAll({
            where: { student_id: studentIds }
        });

        if (existingAlumnis.length > 0) {
            const existingIds = existingAlumnis.map(item => item.student_id);
            throw new Error(`以下学号已存在: ${existingIds.join(', ')}`);
        }

        // 验证手机号是否重复
        const phones = alumniList.map(item => item.phone).filter(phone => phone);
        if (phones.length > 0) {
            const existingPhones = await ctx.model.Alumni.findAll({
                where: { phone: phones }
            });
            if (existingPhones.length > 0) {
                const existingPhoneNumbers = existingPhones.map(item => item.phone);
                throw new Error(`以下手机号已存在: ${existingPhoneNumbers.join(', ')}`);
            }
        }

        // 批量创建 - 默认状态为1（未认证）
        const now = new Date();
        const alumniWithTimestamps = alumniList.map(item => ({
            ...item,
            status: item.status !== undefined ? item.status : 1, // 默认未认证
            created_at: now,
            updated_at: now,
        }));

        const result = await ctx.model.Alumni.bulkCreate(alumniWithTimestamps);
        return result;
    }

    // 获取统计信息
    async getStatistics() {
        const { ctx } = this;

        const total = await ctx.model.Alumni.count();
        const verified = await ctx.model.Alumni.count({ where: { status: 0 } }); // 已认证
        const unverified = await ctx.model.Alumni.count({ where: { status: 1 } }); // 未认证

        // 按学院统计
        const collegeStats = await ctx.model.Alumni.findAll({
            attributes: [
                'college',
                [ctx.Sequelize.fn('COUNT', ctx.Sequelize.col('id')), 'count']
            ],
            group: ['college'],
            raw: true
        });

        // 按年级统计
        const gradeStats = await ctx.model.Alumni.findAll({
            attributes: [
                'grade',
                [ctx.Sequelize.fn('COUNT', ctx.Sequelize.col('id')), 'count']
            ],
            group: ['grade'],
            order: [['grade', 'DESC']],
            raw: true
        });

        return {
            total,
            verified,
            unverified,
            collegeStats,
            gradeStats
        };
    }

    // 校友认证
    async verifyAlumni(studentId, realName, phone) {
        const { ctx } = this;

        // 查找匹配的校友信息
        const alumni = await ctx.model.Alumni.findOne({
            where: {
                student_id: studentId,
                real_name: realName,
                phone: phone
            }
        });

        if (!alumni) {
            throw new Error('校友信息不匹配，认证失败');
        }

        // 更新认证状态为0（已认证）
        await alumni.update({
            status: 0, // 0表示已认证
            updated_at: new Date()
        });

        return alumni;
    }

    // 获取认证状态
    async getVerificationStatus(studentId) {
        const { ctx } = this;

        const alumni = await ctx.model.Alumni.findOne({
            where: { student_id: studentId },
            attributes: ['id', 'student_id', 'real_name', 'status'] // 使用status字段
        });

        if (!alumni) {
            throw new Error('校友不存在');
        }

        return {
            student_id: alumni.student_id,
            real_name: alumni.real_name,
            verified: alumni.status === 0, // status为0表示已认证
            status: alumni.status
        };
    }
}

module.exports = AlumniService;